<?php
/**
 * 导出
 * @author chenlisong E-mail:chenlisong1021@163.com 
 * @version 创建时间：2015年11月12日 上午11:35:59 
 * 类说明
 */
class OutExcel {
	
	/**
	 * 导出
	 */
	static function export($param){
		//导出excel
		if(strcasecmp('excel',$param['type']) == 0){
			OutExcel::exportExcel($param);
			return;
		}
		//导出支付宝模板
		if(strcasecmp('excelAlipay',$param['type']) == 0){
			OutExcel::exportExcelAlipay($param);
			return;
		}
	}
	
	static function exportExcelAlipay($param){
		$fileName = $param['exportFileName'];
		$exportDatas = $param['exportDatas'];
		//设置响应头
		header('pragma:public');
		header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
		header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
		vendor("PHPExcel.PHPExcel");
		$objPHPExcel = new PHPExcel();
		//设置当前Sheet名称
		$objPHPExcel->setActiveSheetIndex(0);
		$objPHPExcel->getActiveSheet()->setTitle($fileName);
		$objPHPExcel->getDefaultStyle()->getFont()->setName("宋体");
		if ($exportDatas != null && count($exportDatas) > 0) {
			$objPHPExcel->getActiveSheet()->setCellValue('A1','批次号');
			$objPHPExcel->getActiveSheet()->setCellValue('B1','总金额（元）');
			$objPHPExcel->getActiveSheet()->setCellValue('C1','总笔数');
			$objPHPExcel->getActiveSheet()->setCellValueExplicit('A2',$exportDatas['code'],PHPExcel_Cell_DataType::TYPE_STRING);
			$objPHPExcel->getActiveSheet()->setCellValueExplicit('B2',$exportDatas['totalPrice'],PHPExcel_Cell_DataType::TYPE_NUMERIC);
			$objPHPExcel->getActiveSheet()->setCellValueExplicit('C2',$exportDatas['totalNum'],PHPExcel_Cell_DataType::TYPE_NUMERIC);
			$objPHPExcel->getActiveSheet()->setCellValue('A3','商户退款流水号');
			$objPHPExcel->getActiveSheet()->setCellValue('B3','支付宝交易号');
			$objPHPExcel->getActiveSheet()->setCellValue('C3','退款金额');
			$objPHPExcel->getActiveSheet()->setCellValue('D3','退款备注');
			$i = 4;
			foreach($exportDatas['subData'] as $key=>$val){
				$objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$i,$val['code'],PHPExcel_Cell_DataType::TYPE_STRING);
				$objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$i,$val['payCode'],PHPExcel_Cell_DataType::TYPE_STRING);
				$objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$i,$val['price'],PHPExcel_Cell_DataType::TYPE_NUMERIC);
				$objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$i,$val['remarker'],PHPExcel_Cell_DataType::TYPE_STRING);
				$i++;
			}
			//设置宽度自适应
			$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
			$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
			$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(strlen('退款金额'));
			$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(strlen('退款备注'));
		}
		//输出
		$xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		$xlsWriter->save("php://output");
	}
	
	/**
	 * 导出excel
	 * $fileName 文件名
	 * $exportDatas 数据
	 * $exportColumns 标题
	 */
	static function exportExcel($param){
		$fileName = $param['exportFileName'];
		$exportDatas = $param['exportDatas'];
		$exportColumns = $param['exportColumns'];
		//设置响应头
		header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
		//创建Excel对象
		vendor("PHPExcel.PHPExcel");
		$objPHPExcel = new PHPExcel();
		//设置当前Sheet名称
		$objPHPExcel->setActiveSheetIndex(0);
		$objPHPExcel->getActiveSheet()->setTitle($fileName);
		//定义边框样式
		$borderStyleArray = array(
			'borders' => array(
				'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN,
					'color' => array('argb' => 'FF333333'),
				),
			),
		);
		//定义最大宽度
		$widths = array();
		//判断一下表头数组是否有数据
		if ($exportColumns != null && count($exportColumns) > 0) {
			//循环写入表头
			$i = 0;
			$j = 1;
			foreach ($exportColumns as $column) {
				$objPHPExcel->getActiveSheet()->setCellValue(OutExcel::dectoletter($i).$j, $column[1]);
				array_push($widths, strlen($column));
				$i++;
			}
			$j++;
			//设置表头样式
			$headerStyle = $objPHPExcel->getActiveSheet()->getStyle("A1:".OutExcel::dectoletter(count($exportColumns)-1)."1");
			$headerStyle->getFont()->setName("微软雅黑");
			$headerStyle->getFont()->setSize(9);
			$headerStyle->getFont()->setBold(true);
			$headerStyle->getFont()->getColor()->setARGB('FFFFFFFF');
			$headerStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$headerStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$headerStyle->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$headerStyle->getFill()->getStartColor()->setARGB('FF1B9ECE');
			$headerStyle->applyFromArray($borderStyleArray);
			//判断表中是否有数据
			if ($exportDatas != null && count($exportDatas) > 0) {
				//循环写入表中数据
				foreach ($exportDatas as $record) {
					$i = 0;
					foreach ($exportColumns as $column) {
						$content = $record[$column[0]];
						$objPHPExcel->getActiveSheet()->setCellValue(OutExcel::dectoletter($i).$j, $content);
						if($widths[$i]<strlen($content)){
							$widths[$i] = strlen($content);
						}
						$i++;
					}
					$j++;
				}
				//设置内容样式
				$contentStyle = $objPHPExcel->getActiveSheet()->getStyle("A2:".OutExcel::dectoletter(count($exportColumns)-1).(count($exportDatas)+1));
				$contentStyle->getFont()->setName("微软雅黑");
				$contentStyle->getFont()->setSize(9);
				$contentStyle->getFont()->getColor()->setARGB('FF333333');
				$contentStyle->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$contentStyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				$contentStyle->applyFromArray($borderStyleArray);
			}
			//冻结首行
			$objPHPExcel->getActiveSheet()->freezePane("A2");
			//自适应宽度
			$i = 0;
			foreach ($exportColumns as $column) {
				$objPHPExcel->getActiveSheet()->getColumnDimension(OutExcel::dectoletter($i))->setWidth($widths[$i]);
				$i++;
			}
		}
		//输出
		$xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 
		$xlsWriter->save("php://output");
	}
	
	/**
	 * 转换数字为26进制字母
	 */
	static function dectoletter($number,$isLower=true){
		$num26 = base_convert($number, 10, 26);
		$addcode = $isLower ? 49 : 17;
		$result = '';
		for ($i=0; $i<strlen($num26); $i++) {
			$code = ord($num26{$i});
			if ($code < 58) {
				$result .= chr($code+$addcode);
			} else {
				$result .= chr($code+$addcode-39);
			}
		}
		return $result;
	}
}